using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core;
using IglooCoder.Commons.WcfNhibernate;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Linq;
using NHibernate.SqlCommand;
using NHibernate.Transform;


namespace Infrastucture
{
    public class PreSalesOrderRepository : BaseNHibernateRepository<PreSalesOrder>
    {
        public PreSalesOrderRepository(ISessionStorage sessionStorage)
            : base(sessionStorage)
        {
        }

        public string GetMaxNumber(string kodeAwal)
        {
            string res = string.Empty;
            var lenKode = kodeAwal.Length + 10;
            var query = Session.CreateSQLQuery("Select Max(PSNo) as Number from PresalesOrder where PSNo like'" + kodeAwal + "%' AND    Len(PSNo)=" + lenKode).AddScalar("Number", NHibernateUtil.String);
            var objRes = query.List()[0];
            if (objRes != null) res = objRes.ToString();
            return res;
        }

        public  void  DeleteDetail(string PSNo)
        {
            var query =  Session.CreateSQLQuery("Delete from PreSalesOrder where PSNo='" + PSNo + "'");
            int res = query.ExecuteUpdate();
        }

        public void UpdateEmailStatus(string PSNo)
        {
            ISQLQuery command =
            Session.CreateSQLQuery("Update PreSalesOrder set HasSent=1 Where PSNo='" + PSNo + "'");
            int res = command.ExecuteUpdate();
        }


        public override IList<PreSalesOrder> GetDataPaged(int page, int pageSize, string sortProperty, string sortType)
        {
            var Results = new List<PreSalesOrder>();
            using (ITransaction tx = Session.BeginTransaction())
            {

                StringBuilder sb = new StringBuilder();
                sb.Append("Select ps.PSNo,SRDate,ps.CustomerName,ps.Description,SINo,so.SONo,ps.Status,do.DONo ");
                sb.Append("from PreSalesOrder ps  ");
                sb.Append("LEFT JOIN SalesOrder so on ps.PSNO=SO.PSNO  ");
                sb.Append("LEFT JOIN DeliveryOrder do ON do.SONo = so.SONO ");
                sb.Append("left Join SalesInvoice si on si.DONo=do.DONo ");
                sb.AppendFormat("Order By ps.{0} {1} ", sortProperty, sortType);

                var query = Session.CreateSQLQuery(sb.ToString())
                                   .SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize)
                                   .SetResultTransformer(Transformers.AliasToBean(typeof(PreSalesOrder)))
                                   ;
              

                var count = Session.CreateSQLQuery("Select Count(*) as recCount from PreSalesOrder").AddScalar("recCount", NHibernateUtil.Int64);
                Results = query.List<PreSalesOrder>().ToList();

                RecordCount = (long)((IList)count.List())[0];
                tx.Commit();
            }

            //RecordCount = Results.TotalCount;
            return Results;
        }


        public override IList<PreSalesOrder> GetDataPaged(int page, int pageSize)
        {
            var Results = new List<PreSalesOrder>();
            using (ITransaction tx = Session.BeginTransaction())
            {
                
                StringBuilder sb = new StringBuilder();
                sb.Append("Select ps.PSNo,SRDate,ps.CustomerName,ps.Description,SINo,so.SONo,ps.Status,do.DONo ");
                sb.Append("from PreSalesOrder ps  ");
                sb.Append("LEFT JOIN SalesOrder so on ps.PSNO=SO.PSNO  ");
                sb.Append("LEFT JOIN DeliveryOrder do ON do.SONo = so.SONO ");
                sb.Append("left Join SalesInvoice si on si.DONo=do.DONo ");

                var query = Session.CreateSQLQuery(sb.ToString())
                                   .SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize)
                                   .SetResultTransformer(Transformers.AliasToBean(typeof(PreSalesOrder)))
                                   ;


                var count = Session.CreateSQLQuery("Select Count(*) as recCount from PreSalesOrder").AddScalar("recCount",NHibernateUtil.Int64);
                Results = query.List<PreSalesOrder>().ToList();

                RecordCount = (long)((IList)count.List())[0];
                tx.Commit();
            }

            //RecordCount = Results.TotalCount;
            return Results;
        }

        public override IList<PreSalesOrder> GetDataPaged(Dictionary<String, Object> criteria, string expression, int page, int pageSize)
        {
            IList<PreSalesOrder> Results = new List<PreSalesOrder>();
            using (var tx = Session.BeginTransaction())
            {
               
                StringBuilder sb2 = new StringBuilder();
                int i = 0;
                var filter = new List<string>();
                foreach (var criterion in criteria)
                    if (!typeof(PreSalesOrder).GetProperties().Any(x => x.Name == criterion.Key)) filter.Add(criterion.Key);

                filter.ForEach((x) =>
                {
                    criteria.Remove(x);
                }
                    );

                if (criteria.Count > 0) sb2.Append(" Where ");

                foreach (var criterion in criteria)
                {
                    if (criterion.Value == null) continue;
                    if (criterion.Value.GetType() == typeof(string))
                        sb2.AppendFormat(" ps." + criterion.Key + " like'%{0}%' ", criterion.Value.ToString());
                    else
                        sb2.AppendFormat(" ps." + criterion.Key + "='{0}' ", criterion.Value);

                    i += 1;
                    if (i < criteria.Count)
                        sb2.Append(expression);
                }


                StringBuilder sb = new StringBuilder();
                sb.Append("Select ps.PSNo,SRDate,ps.CustomerName,ps.Description,SINo,so.SONo,ps.Status,do.DONo ");
                sb.Append("from PreSalesOrder ps  ");
                sb.Append("LEFT JOIN SalesOrder so on ps.PSNO=SO.PSNO  ");
                sb.Append("LEFT JOIN DeliveryOrder do ON do.SONo = so.SONO ");
                sb.Append("left Join SalesInvoice si on si.DONo=do.DONo ");

                var query = Session.CreateSQLQuery(sb.ToString() + sb2.ToString())
                                   .SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize)
                                   .SetResultTransformer(Transformers.AliasToBean(typeof(PreSalesOrder)))
                                   ;

                StringBuilder sb3 = new StringBuilder();
                sb3.Append("Select Count(*) as recCount ");
                sb3.Append("from PreSalesOrder ps  ");
                sb3.Append("LEFT JOIN SalesOrder so on ps.PSNO=SO.PSNO  ");
                sb3.Append("LEFT JOIN DeliveryOrder do ON do.SONo = so.SONO ");
                sb3.Append("left Join SalesInvoice si on si.DONo=do.DONo ");
                var count = Session.CreateSQLQuery(sb3.ToString() + sb2.ToString()).AddScalar("recCount", NHibernateUtil.Int64);
                Results = query.List<PreSalesOrder>().ToList();

                RecordCount = (long)((IList)count.List())[0];
                tx.Commit();
            }

            return Results;
        }

    }
}